package at.ac.tuwien.sepm.s2011.e0208329.dao;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Locale;
import java.util.Map;
import org.apache.log4j.Logger;

import at.ac.tuwien.sepm.s2011.e0208329.db.DatabaseManager;
import at.ac.tuwien.sepm.s2011.e0208329.domain.Dienstleistung;

public class DienstleistungDAOImpl implements DienstleistungDAO { // support the database-access for table "Dienstleistung"
	
	private static final Logger log = Logger.getLogger(DienstleistungDAOImpl.class);
	private DatabaseManager dm;
		
	/**
	 * @param dm Database Manager Singleton
	 */
	public DienstleistungDAOImpl() {
		this.dm = DatabaseManager.getDatabaseManager(); // open the connection to my database
	}

	@Override
	public void insertService(Dienstleistung service) throws SQLException { // Override Super's method, insert a Service into database
		log.debug("Create a new service using the following parameter " + service);
		if (service == null) {
			throw new IllegalArgumentException();
		}
		String query = String.format(Locale.US, "INSERT INTO Dienstleistung(Name, Preis, MwSt, Techniker, Aufwand) VALUES ('%s', %f, %f,'%s', %d)", 
				service.getName(), service.getPrice(), service.getTax(), service.getTechnician(), service.getTimeRequired()); // to be executed
		        // SQL-Command

			log.debug("SQL Statement = " + query);// debug info output
			dm.update(query); // run the method from DatabaseManager to perform the Update-Service
			query = String.format(Locale.US, "SELECT idDienstleistung FROM dienstleistung WHERE name='%s'", service.getName());// select the just 
			ArrayList<ArrayList<String>> rsArray = dm.query(query);  //inserted Service from Database,
			int id = Integer.parseInt(rsArray.get(0).get(0)); // and get it's ID
			log.debug("The new service has ID = " + id);
			service.setIdService(id);			// add the ID to the Service
	}

	@Override
	public ArrayList<Dienstleistung> getAllServices() throws SQLException {// Perform the "select * from Dienstleistung" and return as a List
		ArrayList<Dienstleistung> services = null;
		String query = "SELECT * FROM dienstleistung WHERE DELETED != 1";
			ArrayList<ArrayList<String>> rsArray = dm.query(query); // run the method from DatabaseManager to perform the query-Service
			services = new ArrayList<Dienstleistung>();
			for (ArrayList<String> arrl : rsArray) { // change the data type to Service and add it to a to be returned list
				int id = Integer.parseInt(arrl.get(0));
				boolean deleted = Boolean.parseBoolean(arrl.get(1));
				String name = arrl.get(2);
				double price = Double.parseDouble(arrl.get(3));
				double tax = Double.parseDouble(arrl.get(4));
				String technician = arrl.get(5);
				int time = Integer.parseInt(arrl.get(6));
				int saleNr = Integer.parseInt(arrl.get(7));
				Dienstleistung myService = new Dienstleistung(name, price, tax, technician, time);
				myService.setDeleted(deleted);
				myService.setIdService(id);
				myService.setSaleNumber(saleNr);
				services.add(myService);
			}
		return services;
	}

	@Override
	public ArrayList<Dienstleistung> searchAdvance(
			Map<String, String> searchFields) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Dienstleistung updateService(Dienstleistung service) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override 
	/**
	 * sum the sold No. to the "VerkaufsZahl" 
	 */
	public int setSaleNumber(int serID, int saledNo) throws SQLException {
		String query = "select verkaufszahl from dienstleistung where idDienstleistung=" + serID;
		ArrayList<ArrayList<String>> rsArray = dm.query(query);
		int sold = 0;
		for (ArrayList<String> arrl : rsArray) { //read the "VerkaufsZahl"
			sold = Integer.parseInt(arrl.get(0));
		}
		sold = sold+saledNo;
		log.debug("new sold No. is:"+sold);
		query = "UPDATE dienstleistung SET Verkaufszahl="+sold+" WHERE idDienstleistung="+serID;
		dm.update(query); 
		return sold;
	}

	@Override
	public void deleteService(int serID) throws SQLException { // Mark a Service as "deleted"
		String query = "UPDATE dienstleistung SET deleted=1 WHERE idDienstleistung="+serID;
		dm.update(query); //run the method from DatabaseManager to perform the Update-Service
	}

	@Override
	public int getServiceID(String serName) throws SQLException { // get the Service's ID with specified Service's Name
		String query = "SELECT idDienstleistung from Dienstleistung WHERE Name='"+serName+"'"; // SQL-Command
		ArrayList<ArrayList<String>> rsArray = dm.query(query); //run the method from DatabaseManager to perform the query-Service
		int serID=-1;
		for (ArrayList<String> arrl : rsArray) { // due to the unique property of "name", only one Service will be read
			serID = Integer.parseInt(arrl.get(0)); // transfer the first String to Int as ID
		}
		return serID;
		
	}

	@Override
	public int getPrice(int serID) throws SQLException { // get the Service's Price with specified Service's ID
		String query = "select preis from dienstleistung where idDienstleistung=" + serID;
		ArrayList<ArrayList<String>> rsArray = dm.query(query);
		int myPreis = -1;
		for (ArrayList<String> arrl : rsArray) {
			myPreis = Integer.parseInt(arrl.get(0));
		}
		return myPreis;
	}

	@Override
	public String getSerivceName(int serID) throws SQLException {// get the Service's name with specified Service's ID
		String query = "select name from dienstleistung where idDienstleistung=" + serID;
		ArrayList<ArrayList<String>> rsArray = dm.query(query);
		String name = "";
		for (ArrayList<String> arrl : rsArray) {
			name = arrl.get(0);  // String to String, no need for parse
		}
		return name;
	}


}
